package com.cn.trip.DbDao;

import com.cn.trip.DaoUtil.DBHelper;
import com.cn.trip.bean.Goods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class GoodsDao {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;

	public int addGoods(Goods g) {
		String sql="insert into goods (goods_name,price,imgsrc,stock) values (?,?,?,?)";
		conn= DBHelper.getConnection();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,g.getGoods_name());
			ps.setFloat(2, g.getPrice());
			ps.setString(3, g.getImgSrc());
			ps.setInt(4, g.getStock());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public List<Goods> getGoodsList(){
		String sql="select * from goods";
		conn=DBHelper.getConnection();
		List<Goods> list=null;
		
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			list=new ArrayList<>();
			while(rs.next()) {
				Goods goods=new Goods();
				goods.setId(rs.getInt(1));
				goods.setGoods_name(rs.getString(2));
				goods.setPrice(rs.getFloat(3));
				goods.setImgSrc(rs.getString(4));
				goods.setStock(rs.getInt(5));
				list.add(goods);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public List<Goods> getPageGoods(int startIndex,int pageSize){
		String sql="select * from goods limit ?,?";
		conn=DBHelper.getConnection();
		List<Goods> list=null;
		
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,startIndex);
			ps.setInt(2,pageSize);
			rs=ps.executeQuery();
			list=new ArrayList<>();
			while(rs.next()) {
				Goods goods=new Goods();
				goods.setId(rs.getInt(1));
				goods.setGoods_name(rs.getString(2));
				goods.setPrice(rs.getFloat(3));
				goods.setImgSrc(rs.getString(4));
				goods.setStock(rs.getInt(5));
				list.add(goods);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
		
	public int getGoodsNum(int goods_id) {
		String sql="select stock from goods where id=?";
		conn=DBHelper.getConnection();
			
		
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, goods_id);
			rs=ps.executeQuery();
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	
	public int alterGoodsNum(int goods_id) {
		String sql="update goods set stock=stock-1 where id="+goods_id;
		int flag=0;
		conn=DBHelper.getConnection();
		try {
			ps=conn.prepareStatement(sql);
			flag=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
}
